ALTER VIEW OpenSEEScalarStatView AS
SELECT
    Event.ID AS EventID,
    MeterLocation.Name AS Station,
    Meter.Name AS Meter,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    EventType.Name AS [Event Type],
	CASE BreakerRestrikeField.Value
		WHEN 'Y' THEN 'Yes'
		WHEN 'N' THEN 'No'
		ELSE COALESCE(BreakerRestrikeField.Value, 'N/A')
	END AS [Is Breaker Restrike?],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / 1000.0, '0.###') + ' seconds' AS [File Duration (s)],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / System.Frequency, '0.##') + ' cycles' AS [File Duration (c)],
    FORMAT(FaultSummary.Distance, '0.##') + ' mi' AS [Fault Distance],
    FORMAT(FaultSummary.DurationSeconds * 1000.0, '0') + ' ms' AS [Fault Duration (ms)],
    FORMAT(FaultSummary.DurationCycles, '0.##') + ' cycles' AS [Fault Duration (c)],
    Sag.MagnitudePercent AS [Sag Magnitude (%)],
    Sag.MagnitudeVolts AS [Sag Magnitude (V)],
    FaultSummary.Algorithm,
    FORMAT(EventStat.VPeak, '0') + ' volts' AS [Voltage Peak],
    FORMAT(EventStat.VAMax, '0') + ' volts' AS [VA Maximum],
    FORMAT(EventStat.VBMax, '0') + ' volts' AS [VB Maximum],
    FORMAT(EventStat.VCMax, '0') + ' volts' AS [VC Maximum],
    FORMAT(EventStat.VABMax, '0') + ' volts' AS [VAB Maximum],
    FORMAT(EventStat.VBCMax, '0') + ' volts' AS [VBC Maximum],
    FORMAT(EventStat.VCAMax, '0') + ' volts' AS [VCA Maximum],
    FORMAT(EventStat.VAMin, '0') + ' volts' AS [VA Minimum],
    FORMAT(EventStat.VBMin, '0') + ' volts' AS [VB Minimum],
    FORMAT(EventStat.VCMin, '0') + ' volts' AS [VC Minimum],
    FORMAT(EventStat.VABMin, '0') + ' volts' AS [VAB Minimum],
    FORMAT(EventStat.VBCMin, '0') + ' volts' AS [VBC Minimum],
    FORMAT(EventStat.VCAMin, '0') + ' volts' AS [VCA Minimum],
    FORMAT(EventStat.IPeak, '0') + ' Amps' AS [Current Peak],
    FORMAT(EventStat.IAMax, '0') + ' Amps' AS [IA Maximum],
    FORMAT(EventStat.IBMax, '0') + ' Amps' AS [IB Maximum],
    FORMAT(EventStat.ICMax, '0') + ' Amps' AS [IC Maximum],
    FORMAT(EventStat.IA2t, '0') + ' (A^2)s' AS [IA2t],
    FORMAT(EventStat.IB2t, '0') + ' (A^2)s' AS [IB2t],
    FORMAT(EventStat.IC2t, '0') + ' (A^2)s' AS [IC2t],
    VAN.Mapping AS VAN,
    VBN.Mapping AS VBN,
    VCN.Mapping AS VCN,
    IAN.Mapping AS IAN,
    IBN.Mapping AS IBN,
    ICN.Mapping AS ICN,
    IR.Mapping AS IR
FROM
    Event JOIN
    MeterLine ON
        Event.MeterID = MeterLine.MeterID AND
        Event.LineID = MeterLine.LineID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
    FaultSummary ON
        Event.ID = FaultSummary.EventID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.FaultNumber = 1 LEFT OUTER JOIN
    EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN
    ChannelDetail VAN ON
        Event.MeterID = VAN.MeterID AND
        Event.LineID = VAN.LineID AND
        VAN.MeasurementType = 'Voltage' AND
        VAN.Phase = 'AN' AND
        VAN.MeasurementCharacteristic = 'Instantaneous' AND
        VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VBN ON
        Event.MeterID = VBN.MeterID AND
        Event.LineID = VBN.LineID AND
        VBN.MeasurementType = 'Voltage' AND
        VBN.Phase = 'BN' AND
        VBN.MeasurementCharacteristic = 'Instantaneous' AND
        VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VCN ON
        Event.MeterID = VCN.MeterID AND
        Event.LineID = VCN.LineID AND
        VCN.MeasurementType = 'Voltage' AND
        VCN.Phase = 'CN' AND
        VCN.MeasurementCharacteristic = 'Instantaneous' AND
        VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IAN ON
        Event.MeterID = IAN.MeterID AND
        Event.LineID = IAN.LineID AND
        IAN.MeasurementType = 'Current' AND
        IAN.Phase = 'AN' AND
        IAN.MeasurementCharacteristic = 'Instantaneous' AND
        IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IBN ON
        Event.MeterID = IBN.MeterID AND
        Event.LineID = IBN.LineID AND
        IBN.MeasurementType = 'Current' AND
        IBN.Phase = 'BN' AND
        IBN.MeasurementCharacteristic = 'Instantaneous' AND
        IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail ICN ON
        Event.MeterID = ICN.MeterID AND
        Event.LineID = ICN.LineID AND
        ICN.MeasurementType = 'Current' AND
        ICN.Phase = 'CN' AND
        ICN.MeasurementCharacteristic = 'Instantaneous' AND
        ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IR ON
        Event.MeterID = IR.MeterID AND
        Event.LineID = IR.LineID AND
        IR.MeasurementType = 'Current' AND
        IR.Phase = 'RES' AND
        IR.MeasurementCharacteristic = 'Instantaneous' AND
        IR.SeriesType IN ('Values', 'Instantaneous') CROSS JOIN
    (
        SELECT COALESCE(CONVERT(FLOAT,
        (
            SELECT TOP 1 Value
            FROM Setting
            WHERE Name = 'SystemFrequency'
        )), 60.0) AS Frequency
    ) System OUTER APPLY
    (
        SELECT TOP 1
			FORMAT(Disturbance.PerUnitMagnitude * 100, '0.0') + '%'  AS MagnitudePercent,
            FORMAT(Disturbance.Magnitude, '0') + ' volts' AS MagnitudeVolts
        FROM
            Disturbance JOIN
            EventType ON
                Disturbance.EventTypeID = EventType.ID AND
                EventType.Name = 'Sag' JOIN
            Phase ON
                Disturbance.PhaseID = Phase.ID AND
                Phase.Name = 'Worst'
        WHERE
            Disturbance.EventID = Event.ID AND
            Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND
            Disturbance.EndTime >= FaultSummary.Inception
    ) Sag LEFT OUTER JOIN
	(
		SELECT
			FileGroupFieldValue.FileGroupID,
			FileGroupFieldValue.Value
		FROM
			FileGroupField JOIN
			FileGroupFieldValue ON FileGroupFieldValue.FileGroupFieldID = FileGroupField.ID
		WHERE FileGroupField.Name = 'BreakerRestrike'
	) BreakerRestrikeField ON BreakerRestrikeField.FileGroupID = Event.FileGroupID
GO

ALTER PROCEDURE [dbo].[selectSitesBreakersDetailsByDate]
    @EventDate AS DATETIME,
    @MeterID AS NVARCHAR(MAX),
    @username AS NVARCHAR(4000),
    @context as nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @startDate DateTime
    DECLARE @endDate DateTime

    IF @context = 'day'
    BEGIN
        SET @startDate = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @endDate = DATEADD(DAY, 1, @startDate)
    END

    if @context = 'hour'
    BEGIN
        SET @startDate = DATEADD(HOUR, DATEDIFF(HOUR, 0, @EventDate), 0)
        SET @endDate = DATEADD(HOUR, 1, @startDate)
    END

    if @context = 'minute'
    BEGIN
        SET @startDate = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, @EventDate), 0)
        SET @endDate = DATEADD(MINUTE, 1, @startDate)
    END

    if @context = 'second'
    BEGIN
        DECLARE @tempDate DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, @EventDate), 0)
        SET @startDate = DATEADD(SECOND, DATEDIFF(SECOND, @tempDate, @EventDate), @tempDate)
        SET @endDate = DATEADD(SECOND, 1, @startDate)
    END

	select * into #meterSelection from  dbo.String_to_int_table(@MeterID, ',')

    SELECT
        Meter.ID AS meterid,
        Event.ID AS theeventid,
        EventType.Name AS eventtype,
        BreakerOperation.ID AS breakeroperationid,
        CAST(CAST(BreakerOperation.TripCoilEnergized AS TIME) AS NVARCHAR(100)) AS energized,
        BreakerOperation.BreakerNumber AS breakernumber,
        MeterLine.LineName AS linename,
        Phase.Name AS phasename,
        CAST(BreakerOperation.BreakerTiming AS DECIMAL(16,5)) AS timing,
        CAST(BreakerOperation.StatusTiming AS DECIMAL(16,5)) AS statustiming,
        BreakerOperation.BreakerSpeed AS speed,
        BreakerOperation.StatusBitChatter AS chatter,
        BreakerOperation.DcOffsetDetected AS dcoffset,
        BreakerOperationType.Name AS operationtype,
        (SELECT COUNT(*) FROM EventNote WHERE EventNote.EventID = Event.ID) as notecount
    FROM
        BreakerOperation JOIN
        Event ON BreakerOperation.EventID = Event.ID JOIN
        EventType ON EventType.ID = Event.EventTypeID JOIN
        Meter ON Meter.ID = Event.MeterID JOIN
        Line ON Line.ID = Event.LineID JOIN
        MeterLine ON MeterLine.LineID = Event.LineID AND MeterLine.MeterID = Meter.ID JOIN
        BreakerOperationType ON BreakerOperation.BreakerOperationTypeID = BreakerOperationType.ID JOIN
        Phase ON BreakerOperation.PhaseID = Phase.ID
    WHERE
        TripCoilEnergized >= @startDate AND TripCoilEnergized < @endDate AND
        Meter.ID IN (SELECT * FROM  #meterSelection)
END
GO